
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'UpdateEmailsFromRex')
	BEGIN
		PRINT 'Dropping UpdateEmailsFromRex'
		DROP  Procedure  [dbo].[UpdateEmailsFromRex]
	END

GO

PRINT 'Creating UpdateEmailsFromRex'
GO
/******************************************************************
* VSS File Path: $Archive: /PHSRAG/Applications/WebApplications/Insight_2.2.0/Database/StoredProcedures/FeedStoredProcedures/UpdateEmailsFromRex.sql $
* Name:  UpdateEmailsFromRex
* Purpose:  
* Updates email information in Insight from Rex values, excluding
*	any records that have been manually updated in Insight.
*
* PARAMETERS
* Name    			Description					
* -------------  	---------------------------------------
* N/A				N/A
*
* RESULT SET
* Name    			Description					
* --------------	-------------------------------------------
* N/A				n/a
*
*
* RETURN VALUE
* Value    			Description					
* --------------	-------------------------------------------
* N/A				N/A
*
******************************************************************/
CREATE Procedure [dbo].[UpdateEmailsFromRex] 
AS

BEGIN

	SET NOCOUNT ON


	BEGIN
		-- First, any emails that are different, but blank in Insigh, update the blank ones to be the value from Rex.
		Update AdmEmailAddress set EmailAddress = S.StagingEmail FROM (
		select S.EmailAddress as StagingEmail, E.EmailAddress as InsightEmail, E.Id as AdmEmailAddressId
		from Stg_RexEmails S
		inner join AdmPerson A on A.Id = S.ADmPersonId
		inner join AdmEmailAddress e On E.AdmPersonId = A.Id
		where AdmEmailAddressTypeId = 1 AND
			DoNotUpdateEmail = 0 AND
			UPPER(S.EmailAddress) != UPPER(E.EmailAddress)
			AND E.EmailAddress = '') S
		WHERE S.AdmEmailAddressId = AdmEmailAddress.Id AND
			(AdmEmailAddress.EmailAddress = '')
	END

	BEGIN
		-- see if any of the Rex emails are in the person's email list as non-primary. If they are, 
		-- flip the status. So first, update the non-primary flag.
		BEGIN
			UPDATE Stg_RexEmails set existsNonPrimary = 1 where EmailAddress in (
			select S.EmailAddress 
			from Stg_RexEmails S
			inner join AdmPerson A on A.Id = S.ADmPersonId
			inner join AdmEmailAddress e On E.AdmPersonId = A.Id
			where AdmEmailAddressTypeId != 1 AND
				UPPER(S.EmailAddress) != UPPER(E.EmailAddress) )
		END

		-- set them all as non-primary for the appropriate people
		BEGIN
			UPDATE AdmEmailAddress set AdmEmailAddressTypeId = 2 where AdmPersonId in (
				select E.AdmPersonId from AdmEmailAddress E
					inner join Stg_RexEmails S
						on UPPER(S.EmailAddress) = UPPER(E.EmailAddress)
				WHERE S.ExistsNonPrimary = 1)
				AND DoNotUpdateEmail  = 0
		END

		--- Now mark the ones as primary
		BEGIN
			UPDATE AdmEmailAddress set AdmEmailAddressTypeId = 1 where Id in (
				select E.Id from AdmEmailAddress E
					inner join Stg_RexEmails S
						on UPPER(S.EmailAddress) = UPPER(E.EmailAddress)
				WHERE S.ExistsNonPrimary = 1)
				AND DoNotUpdateEmail  = 0
		END
	END

	BEGIN
		-- now set the current ones to secondary and insert the remaining ones...
		UPDATE AdmEmailAddress set AdmEmailAddressTypeId = 2 where AdmPersonId in (
			select A.Id
				from Stg_RexEmails S
				inner join AdmPerson A on A.Id = S.ADmPersonId
				inner join AdmEmailAddress e On E.AdmPersonId = A.Id
				where AdmEmailAddressTypeId = 1 AND
					UPPER(S.EmailAddress) != UPPER(E.EmailAddress))
			AND DoNotUpdateEmail = 0
		
		-- set those rows to be inserted
		Update Stg_RexEmails set ToBeInserted = 1 where EmailAddress in (
			SELECT S.EmailAddress
			FROM Stg_RexEmails S
			Where UPPER(S.EmailAddress) not in (select distinct(UPPER(EmailAddress)) from AdmEmailAddress))

		-- insert	
		INSERT INTO AdmEmailAddress ( EmailAddress, AdmPersonId, AdmEmailAddressTypeId, DoNotUpdateEmail ) 
			SELECT S.EmailAddress, S.AdmPersonId, 1, 1
			FROM Stg_RexEmails S
			Where S.ToBeInserted = 1

	END

	BEGIN
		BEGIN
		-- Update those emails where there are two non-primaries but no primary (Rex allows this)
		update AdmEmailAddress set AdmEmailAddressTypeId = 1 where Id in (
			select max(Id) From AdmEmailAddress where AdmPersonId in (
				select admpersonId from AdmEmailAddress
				where AdmEmailAddressTypeId = 2	
				and AdmPersonId not in 
					(select distinct(AdmPersonId) from AdmEmailAddress where AdmEmailAddressTypeId = 1)
				group by AdmPersonId, AdmEmailAddressTypeId
				having count(AdmPersonId) > 1)
			group by admPersonId)
			AND DoNotUpdateEmail = 0
		END

		BEGIN
		-- Now update those with only one email to set that as the primary
		update AdmEmailAddress set AdmEmailAddressTypeId = 1 where Id in (
			select max(Id) From AdmEmailAddress where AdmPersonId in (
				select admpersonId from AdmEmailAddress
				where AdmEmailAddressTypeId = 2	
				and AdmPersonId not in 
					(select distinct(AdmPersonId) from AdmEmailAddress where AdmEmailAddressTypeId = 1)
				group by AdmPersonId, AdmEmailAddressTypeId
				having count(AdmPersonId) = 1)
			group by admPersonId)
		END
	END


	-- Check errors
	IF @@ERROR <> 0 
	BEGIN
		RAISERROR ('Failed to update emails from Rex. Error = %s.', 16, 1, @@ERROR)
	END
		
	SET NOCOUNT OFF
END
GO


